//
//  DemoDBManager.swift
//  rdb-led-banner
//
//  Created by radiobox3 on 2025/4/23.
//

import SQLite

class DemoDBManager: NSObject{
    
    static let shard = DemoDBManager()
    var dataBase: Connection?
    let table: Table = Table("tableName")
    let id = SQLite.Expression<String>("id")
    let fieldName = SQLite.Expression<String>("fieldName")
    let timestamp = SQLite.Expression<Int64>("timestamp")
    
    
    /// 创建表
    override init() {
        super.init()
        let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
        do{
            try self.dataBase = Connection("\(path)/tableName.db")
        }catch{
            print(error)
        }
        try! self.dataBase?.run(table.create(ifNotExists: true, block: { table in
            table.column(self.id,primaryKey: true)
            table.column(self.fieldName)
            table.column(self.timestamp)
        }))
    }
    
    ///增
    func save(id: String,fieldName: String,complete: ((_ isSuccess: Bool) -> Void)?){
        let insert = self.table.insert(or: .replace,
                                       self.id <- id,
                                       self.fieldName <- fieldName,
                                       self.timestamp <- Int64(Date().timeIntervalSince1970 * 1000))
        do{
            try self.dataBase?.run(insert)
            complete?(true)
        }catch{
            print(error)
            complete?(false)
        }
    }
    ///删
    func del(id: String, complete: ((_ isSuccess: Bool) -> Void)?){
        let delete = self.table.filter(self.id == id).delete()
        do{
            try self.dataBase?.run(delete)
            complete?(true)
        }catch{
            print(error)
            complete?(false)
        }
    }
    ///改
    func update(id: String, fieldName: String, complete: ((_ isSuccess: Bool) -> Void)?){
        let update = self.table.filter(self.id == id)
            .update(self.fieldName <- fieldName,
                    self.timestamp <- Int64(Date().timeIntervalSince1970 * 1000))
        do{
            try self.dataBase?.run(update)
            complete?(true)
        }catch{
            print(error)
            complete?(false)
        }
    }
    ///查
    func query(size: Int, offset: Int) -> [Any] {
        var resultArr: Array<Any> = []
        let query = self.table.select(self.id, self.fieldName,self.timestamp)
            .filter(self.id == "1")
            .order(self.timestamp.desc)
            .limit(size, offset: offset)
        if let result: AnySequence<Row> = try? self.dataBase?.prepare(query) {
            result.forEach{ row in
                /// 解析结果 可以将其封装到一个实体类
                if let id = try? row.get(self.id){
                    
                }
                if let fieldName = try? row.get(self.fieldName){
                    
                }
                ///resultArr.append(<#T##newElement: Any##Any#>)
                
            }
        }
        return resultArr
    }
}
